<?php

namespace Home\Controller;
use Think\Controller;

//用户中心全局控制
class ExcelController extends  Controller {

    /**
     * 发送邮件，带Excel附件
     * 
     * 
     */
	
 private function getsendtime(){
 	$cache_sendemail= APP_PATH."/Runtime/Data/EveryDaySendEmail.php";
 	$flag=0;
 	if(file_exists($cache_sendemail))
 	{
 		include $cache_sendemail;
 		$ret= json_decode($json,true);
 		$date = $ret['addtime'];  
 		$stime=strtotime($ret['day']." 00:00:01");
 		$ctime=strtotime(date("Y-m-d")." 00:00:01");
 		if (time() >= intval($date) && $stime < $ctime) {
 			$flag=1;
 		} 
 	}
 	else{
 	    $flag=1;
 	} 
 	
 	$keyword="$"."json='".urldecode("{\"day\":\"".date("Y-m-d")."\",\"addtime\":\"".time()."\"}")."';";
 	buildCache($cache_sendemail,$keyword);
 	
 	return $flag;
 }
 
 public function save_red_excel(){
     $flag=$this->getsendtime();
     if($flag==0){exit('{"error"=>0,"message"=>"已经执行"}');}
    //每天扫描51M数据库，若51M的实际库存少于警告库存。扫描goods表
	$db=M("procreq");
	
	//查询澳买网数据库
	$db_aomai=AoMai("goods");
	//$goods=$db_aomai->where("goods_number <= 2 and is_delete = 0 and is_on_sale = 1")->field("goods_sn,goods_englishname,warn_number,goods_number,'aomai' as source")->select();  
	//澳买的可售库存(实际库存-待发货库存) 小于 警告库存，且51M的实际库存为0
	
	//澳买的可售库存(实际库存-待发货库存) 小于 警告库存
	$sql="SELECT goods_sn,goods_englishname,warn_number,goods_number,'aomai' AS source,IFNULL(b.sale_number,0) AS sale_number,bar_code FROM ki_goods AS a left JOIN(
			SELECT og.`goods_id`, SUM(goods_number) AS sale_number FROM ki_order_goods  AS og INNER JOIN ki_order_info AS oi ON og.order_id=oi.order_id
			WHERE (oi.order_status=1 OR oi.order_status=5 OR oi.order_status=6) AND (oi.shipping_status=0 OR oi.shipping_status=3 OR oi.shipping_status=5 OR oi.shipping_status=4 )
			GROUP BY og.`goods_id`) AS b ON a.goods_id=b.goods_id  WHERE a.is_delete = 0 AND a.is_on_sale = 1 AND a.`warn_number`>(a.`goods_number`-IFNULL(b.sale_number,0))";
	
	$goods= $db_aomai->query($sql);
	
	//澳买可售库存 小于警告库存 2016-09-20处理。
	
	/* $ids="0";
	if($goods_list){
		foreach($goods_list as $k=>$v){
		   $ids.=",".$v['goods_sn'];
		} 
		
	   $goods_510=M("goods")->where("goods_number=0 and FIND_IN_SET(goods_sn,'".$ids."')")->field("goods_sn,goods_englishname,warn_number,goods_number,'51M' as source")->select();
		
	   foreach($goods_list as $k=>$v){
	   	   $flg=false;
		    foreach ($goods_510 as $key=>$o){
		    	if(trim(strtolower($v['goods_sn'])) == trim(strtolower($o['goods_sn']))){
		    		$flg=true;
		    	}
		    }
		    if($flg){
		    	$goods[]=$v;
		    }
		} 
	}  */
	
	//查询51M数据库，并过滤澳买网的商品信息 
   // $goods_51m=M("goods")->where("warn_number > goods_number and is_delete = 0 and is_on_sale = 1")->field("goods_sn,goods_englishname,warn_number,goods_number,'51M' as source")->select();
    
	$sql_51m="SELECT goods_sn,goods_englishname,warn_number,goods_number,'51M' AS source,IFNULL(b.sale_number,0) AS sale_number,bar_code FROM ki_goods AS a left JOIN(
			SELECT og.`goods_id`, SUM(goods_number) AS sale_number FROM ki_order_goods  AS og INNER JOIN ki_order_info AS oi ON og.order_id=oi.order_id
			WHERE (oi.order_status=1 OR oi.order_status=5 OR oi.order_status=6) AND (oi.shipping_status=0 OR oi.shipping_status=3 OR oi.shipping_status=5 OR oi.shipping_status=4 )
			GROUP BY og.`goods_id`) AS b ON a.goods_id=b.goods_id  WHERE a.is_delete = 0 AND a.is_on_sale = 1 AND a.`warn_number`>(a.`goods_number`-IFNULL(b.sale_number,0))";
	
	$goods_51m=M("goods")->query($sql_51m);
	
    $arytemp=array();
    //合并数据信息
    foreach ($goods_51m as $k=>$v){
        $flg=false;
    	foreach ($goods as $key=>$o){
    		//比较注意转化小写，过滤前后空格
    		if(trim(strtolower($v['goods_sn'])) == trim(strtolower($o['goods_sn']))){
    			$flg=true;
    			break;
    		} 
    	}
    	if(!$flg)
    	  $arytemp[]=$v;
    }
     
    foreach ($arytemp as $k=>$v){
    	$goods[]=$v;
    }
    
     
   /*
     //不能跨域查询，同域跨库查询
    $sql="SELECT goods_sn,goods_englishname,warn_number,goods_number,'aomai' AS source FROM `kangarooisland_database`.ki_goods WHERE warn_number > goods_number AND is_delete = 0 AND is_on_sale = 1
      UNION
      SELECT  goods_sn,goods_englishname,warn_number,goods_number,'51M' AS source FROM `51m_database`.ki_goods WHERE warn_number > goods_number AND is_delete = 0 AND is_on_sale = 1 AND goods_sn NOT IN(SELECT goods_sn FROM `kangarooisland_database`.ki_goods WHERE warn_number > goods_number AND is_delete = 0 AND is_on_sale = 1)";
    $goods=M()->query($sql);
    */
    
	$firsttime=date("Y-m-d 00:00:01",strtotime("-1 day"));
	$lasttime=date("Y-m-d 23:59:59",strtotime("-1 day"));

	//$procreqs=$db->where("procreq_time BETWEEN '".$firsttime."' and '".$lasttime."'")->select();
	$procreqs=$db->where("demand_time > 0")->select();

	     $aryupd=$aryadd=array();
		 //提交数据 是否在数据库中存在，存在更新否则增加
		 //$i=0;
		 foreach($goods as $k=>$v){
			      $flg=0;
                  $data['goods_englishname']=empty($v['goods_englishname'])?"nothing":str_replace("'","\'",$v['goods_englishname']);
				  $data['warning_stock_quantity']=$v['warn_number'];
				  $data['actual_stock_quantity']=$v['goods_number'];
				  $data['actual_goods_sn']=$v['goods_sn'];
				  $data['procreq_time']=date("Y-m-d H:i:s");		 
				  $data['actual_date']=date("Y-m-d H:i:s");
				  $data['source']=$v['source'];
				  $data['bar_code']=$v['bar_code']; 
		 	  foreach ($procreqs as $key=>$o){
		 	  if($v['goods_sn']==$o['actual_goods_sn']){
				  $data["demand_time"]=$o["demand_time"]+1;
				  $data['actual_date']=$o['actual_date'];
				  $flg=1;
		 	  	  break;
		 	  }
		 	  }
			    if($flg==0)
				{
					$data["demand_time"]=1;
					//$data['actual_date']=date("Y-m-d H:i:s");
					//$data['actual_date']=date("Y-m-d H:i:s",time()+$i);
				}
		     	$aryadd[]=$data;
				//$i++;
		 }
          foreach ($procreqs as $key=>$o){
			   $flg=0;
			   foreach($goods as $k=>$v){
                   if($o['goods_sn']==$v['actual_goods_sn'])
				   {
					   $flg=1;
		 	  	       break; 
				   }					   
			   }
			   if($flg==0)
			   {
				   $aryupd[]=$o['id'];
			   }
		  }

         //构建更新数据语句		  
		  if(count($aryupd)>0){
			  foreach($aryupd as $key =>$val)
			  {
				  $data['demand_time']=0;
				  $db->where("id ='".$val."'")->save($data);
			  }
		  }
		//构建插入数据语句
		 if(count($aryadd)>0){
		 	$sqlval=""; 
		 	foreach ($aryadd as $k=>$v){
		 		//$listid=getGuid();//创建guid值
		 		$sqlval.=((empty($sqlval)?"":",")."('".$v['goods_englishname']."',".$v['warning_stock_quantity'].",".$v['actual_stock_quantity'].",'".$v['procreq_time']."','".$v['actual_goods_sn']."',".$v['demand_time'].",'".$v['actual_date']."','".$v['source']."','".$v['bar_code']."')");
		 	}
		 	
	       $sqladd=" INSERT INTO  ".C('DB_PREFIX')."procreq(goods_englishname,warning_stock_quantity,actual_stock_quantity,procreq_time,actual_goods_sn,demand_time,actual_date,source,bar_code) values ".$sqlval;
		   if($sqladd){ 
		        $db->execute($sqladd);
		     } 
		 }


		 
	// exportExcel($expTitle,$expCellName,$expTableData)
	$xlsName  = C("XLSNAME");
	$save_xlsName  = C("SAVE_XLSNAME");
    $xlsCell  = array(
            array('actual_goods_sn','商品的货号'),
            array('goods_englishname','商品的英文名称'),
            array('warning_stock_quantity','警告库存数量'),
            array('actual_stock_quantity','实际库存数量'),
            array('demand_time','需求提出天数'),
            array('actual_date','需求提出日期'),
    		array('bar_code','条形码'),
    		array('source','需求来源')  
            );
    //获取Excel内容
    $todatytime=date("Y-m-d 00:00:01");
	$results=$db->where(" procreq_time > '".$todatytime."'")->field("bar_code,actual_goods_sn,goods_englishname,warning_stock_quantity,actual_stock_quantity,demand_time,actual_date,source")->order("source desc,demand_time desc,warning_stock_quantity desc")->select();
	if(!$results)
	{
		//exit("没有需要采购的商品");
		exit('{"error"=>0,"message"=>"没有需要采购的商品"}');
	}
	//print_r($results);
	$path="./Public/Excel/".$save_xlsName."_".date("Y-m")."/";
	//exportExcel(),返回保存的Excel文件的路径
	
	$save_path=exportExcel($xlsName,$xlsCell,$results,$path);
	
	require_once($_SERVER['DOCUMENT_ROOT'].'/includes/email/aomai_email.php');
	$email=new \AoMai_email();
	
	// weimek_emailfun_atta($to, $subject = "", $body = "",$attachmentpath="")
	// $to 表示收件人地址, $subject 表示邮件标题 ,$body表示邮件正文 ,$attachmentpath 附件地址。
	
	//获取邮件收件人
	
	$where['isactive']=1;
	$where['isrecipient']=1;
	$where['type']=array("like","%采购需求%");
	$result=M("mailing_list")->where($where)->select();
	if(!$result)
	{
		exit('{"error"=>0,"message"=>"没有邮件接收人"}');
	}
	
		$email_arr['subject']="51M每日采购需求表报告".date("Y-m-d");
		$email_arr['attachmentpath']=$save_path;
        $email_arr['body']="Dear all!\n\r每日采购需求表报告,请查收附件！\n\r谢谢！\n\r".date("Y-m-d");
	//mailing_address:邮件地址,mail_name:邮件名称
	foreach($result as $key => $val)
	{
		$arr[$key]['to']=$val['mailing_address'];
		$arr[$key]['subject']=$val['mail_name'];
		
	}
	$email_arr['people']=$arr;
	$result=$email->weimek_emailfun_atta_arr($email_arr);
	//$result=array("error"=>0,1,"message"=>),error =1 ,表示成功，为0，表示失败，message为返回信息
	
	$ret="{\"statue\":\"".$result['error']."\",\"msg\":\"".$result['message']."\"}";
    exit($ret);
	
	}
	
	
	//获得包团购结果
	
	public function get_package_res(){
		
		$dbprefix=C('DB_PREFIX');
		$xlsName  = "51M-group";
		$save_xlsName  = "51M_group";
		$xlsCell  = array(
				array('packagename','团包名称'),
				array('blongtype','包类型'),
				array('goods_name','商品名称'),
				array('sum','商品购买数量'),
				array('fact_sum','商品实际采购数量'),
				);
				
		$path="./Public/Excel/".$save_xlsName."_".date("Y-m")."/";		
		//获得包的购买数量
		//$sql="count(*),packageid from ";
		/* $where['gpp.isactive']=1;
		$where['gpp.endtime']=array("gt",date("Y-m-d H:i:s"));
		$where['gpp.blongtype']=array(array(array("eq","Group"),$where['gpp.isaccord']=1),array("eq","presell"),"or");
		$where['gpo.status']=1;
		$where['og.is_on_pre_sale']=array("gt",1); */
		//$where="gpp.isactive = 1 and gpp.endtime > '".date("Y-m-d H:i:s")."' and gpo.status = 1 and ((gpp.blongtype = 'Group' AND gpp.isaccord  = 1 ) OR gpp.blongtype = 'presell' ) AND gpo.status = 1 AND og.is_on_pre_sale > 1";
		$where="gpp.isactive = 1 and gpp.endtime > '".date("Y-m-d H:i:s")."'  and gpp.is_complete = 0 and gpo.status = 1  AND og.is_on_pre_sale > 1";
		$m=M("group_purchase_package")->alias("gpp")->join(" inner join ".$dbprefix."group_package_order as gpo on gpp.packageid = gpo.packageid  inner join ".$dbprefix."order_goods as og on og.order_id = gpo.orderid")->where($where)->field("gpp.packagename,if(gpp.blongtype='Group','团购','预售') as blongtype,gpp.packageid,gpp.isaccord,gpo.orderid,gpo.userid,sum(og.goods_number) as sum,og.goods_name,og.goods_id")->order("gpp.addtime desc")->group("gpp.packageid,og.goods_id")->select();
		//根据orderid 去查询购买的商品的数量
		//echo M("group_purchase_package")->getLastSql();
		//var_dump($m); 
		//exit;
		foreach($m as $key => $val){
			if($val['blongtype'] == "团购" && $val['isaccord'] == 1)
			{
				$packageid.="'".$val['packageid']."',";
		    }
		}
		$packageid=rtrim($packageid,",");
		//echo $packageid;
		if($packageid)
		{

			//$where="gpp.isactive = 1 and gpp.endtime > '".date("Y-m-d H:i:s")."' and gpo.status = 1 and gpp.blongtype = 'Group' AND gpp.isaccord  = 1 AND og.is_on_pre_sale > 1";
			$wheres="gpp.packageid in (".$packageid.") and gpo.status = 1 AND og.is_on_pre_sale > 1";
			//$ms=M("group_purchase_package")->alias("gpp")->join("inner join  ".$dbprefix."group_package_goods as gpg on gpp.packageid = gpg.packageid inner join ".$dbprefix."group_package_order as gpo on gpp.packageid = gpo.packageid  inner join ".$dbprefix."order_goods as og on og.order_id = gpo.orderid")->where($wheres)->field("gpp.packagename,gpp.total,gpp.packageid,gpp.totalquantity*sum(gpg.number) as goods_num, gpo.orderid,gpo.userid,sum(og.goods_number) as sum,count(gpo.orderid) as count,gpo.listid,og.goods_id")->order("gpp.addtime desc")->group("og.goods_id,gpo.listid")->having("count(gpo.orderid) < gpp.total or sum < goods_num")->select();
			$ms=M("group_purchase_package")->alias("gpp")->join(" inner join ".$dbprefix."group_package_order as gpo on gpp.packageid = gpo.packageid  inner join ".$dbprefix."order_goods as og on og.order_id = gpo.orderid")->where($wheres)->field("gpp.packagename,gpp.total,gpp.packageid,gpp.totalquantity*(select sum(gpg.number) as num from ".$dbprefix."group_package_goods as gpg where gpg.packageid = gpp.packageid) as goods_num,gpo.orderid,gpo.userid,sum(og.goods_number) as sum,count(gpo.orderid) as count,gpo.listid,og.goods_id")->order("gpp.addtime desc")->group("og.goods_id,gpo.listid")->having("count(gpo.orderid) < gpp.total or sum < goods_num")->select();
				
		}	
		
       // echo "</pre>";		
		//print_r($ms);
		foreach($m as $key => $val){
			$m[$key]["fact_sum"]=$val['sum'];
			$sum=0;
			foreach($ms as $key1 => $val1){
				if($val['packageid'] == $val1['packageid'] && $val['goods_id'] == $val1['goods_id'])
				{
					$sum=$sum+$val1['sum'];
				}
			}
			$m[$key]["fact_sum"]=$val['sum']-$sum;
			
		}
	    $save_path=exportExcel($xlsName,$xlsCell,$m,$path);
		//exit;
		//print_r($m); 
       // exit;
		$where_gl="gpp.isactive = 1 and gpp.endtime > '".date("Y-m-d H:i:s")."' and gpp.blongtype = 'Group' and gpp.is_complete = 0";
		$mgl=M("group_purchase_package")->alias("gpp")->join(" inner join ".$dbprefix."group_list as gl on gl.packageid = gpp.packageid inner join ".$dbprefix."users as u on u.user_id = gl.userid")->where($where_gl)->field("gpp.packagename,gpp.packageid,if(gpp.blongtype='Group','团购','预售') as blongtype,gpp.total,gpp.totalquantity*(select sum(gpg.number) as num from ".$dbprefix."group_package_goods as gpg where gpg.packageid = gpp.packageid) as totalquantity,gl.listid,gl.iscreate,if(u.nickname='',u.user_name,u.nickname) as nickname")->order("gpp.addtime desc")->select();
		//echo M("group_purchase_package")->getLastSql();
		$mu=M("group_purchase_package")->alias("gpp")->join(" inner join ".$dbprefix."group_package_order as gpo on gpp.packageid = gpo.packageid  inner join ".$dbprefix."order_goods as og on og.order_id = gpo.orderid  inner join ".$dbprefix."users as u on u.user_id = gpo.userid")->where($where)->field("gpp.packagename,gpp.total,gpp.endtime,gpp.packageid,if(gpp.blongtype='Group','团购','预售') as blongtype,gpo.orderid,gpo.userid,gpo.listid,sum(og.goods_number) as sum,og.goods_name,if(u.nickname='',u.user_name,u.nickname) as nickname")->order("gpp.addtime desc")->group("gpo.orderid,og.goods_id")->select();
		//var_dump($mgl);
		//exit;
		//根据orderid 去查询购买的商品的数量
		$data=array();
		$j=0;
		foreach($mgl as $key => $val){
			$i=$sum=$k=$flag=0;
			$userid=array();
			$data[$j]['packagename']=$val['packagename'];
			$data[$j]['blongtype']=$val['blongtype'];
			$data[$j]['goods_name']="";
			$data[$j]['num']=0;
			$data[$j]['nickname']=$val['nickname'];
			if($val['iscreate'] == 1)
			$data[$j]['isadmin']='后台发起人员';
		    else
			$data[$j]['isadmin']='前台用户';
			$data[$j]['iscreate']='是';
			$data[$j]['issuccess']='';
		   foreach($mu as $key1 => $val1){
			   if($val['packageid'] == $val1['packageid'] && $val['listid'] == $val1['listid'])
			   {
				   $j++;
				   if(!in_array($val1['userid'],$userid))
				   {
					   $userid[$k]=$val1['userid'];
					   $k++;
				   }
				   $data[$j]['packagename']=$val['packagename'];
				   $data[$j]['blongtype']=$val1['blongtype'];
				   $data[$j]['goods_name']=$val1['goods_name'];
				   $data[$j]['num']=$val1['sum'];
				   $data[$j]['nickname']=$val1['nickname'];
				   $data[$j]['isadmin']='参团人';
				   $data[$j]['iscreate']='参团人';
				   $data[$j]['issuccess']='';
				   $i++;
				   $sum=$sum+$val1['sum'];
			   }
		   }
           if(count($userid) == $val['total'] )
		   {
			   $data[$j-$i]['issuccess']='团购成功';
		   }elseif($sum >= $val['totalquantity'])
		   {
			   $data[$j-$i]['issuccess']='团购成功';
		   }
		   else{
			   $data[$j-$i]['issuccess']='团购失败';
		   }
		   $j++;
           //echo count($userid)."+".$val['total']."<br />";	   
		}
		//var_dump($data);
		//exit;
		//会员和团包的关系，这个团有那些会员买了，个数是多少
		$xlsName_user  = "51M-group-user";
		//$save_xlsName  = "51Mgroup";
		$xlsCell_user  = array(
				array('packagename','团包名称'),
				array('blongtype','包类型'),
				array('goods_name','商品名称'),
				array('num','商品购买数量'),
				array('nickname','购买人员'),
				array('isadmin','发起人类型'),
				array('iscreate','是否是发起人'),
				array('issuccess','团状态'),
				);
		//exportExcel(),返回保存的Excel文件的路径
		
		$save_path_user=exportExcel($xlsName_user,$xlsCell_user,$data,$path);
		
		//获取邮件收件人
		$where='';	
		$where['isactive']=1;
		$where['isrecipient']=1;
		$where['type']=array("like","%51m团购预%");
		$result=M("mailing_list")->where($where)->select();
		if(!$result)
		{
				exit('{"error"=>0,"message"=>"没有邮件接收人"}');
		}
		require_once($_SERVER['DOCUMENT_ROOT'].'/includes/email/aomai_email.php');
	    $email=new \AoMai_email();	
		$email_arr['subject']="51M预售团购表报告".date("Y-m-d");
		$email_arr['attachmentpath']=array($save_path,$save_path_user);
		$email_arr['body']="Dear all!\n\r每日预售团购表报告,请查收附件！\n\r谢谢！\n\r".date("Y-m-d");
		//mailing_address:邮件地址,mail_name:邮件名称
		foreach($result as $key => $val)
		{
			$arr[$key]['to']=$val['mailing_address'];
			$arr[$key]['subject']=$val['mail_name'];
				
		}
		$email_arr['people']=$arr;
		$result=$email->weimek_emailfun_atta_arr($email_arr);
		
	    //$result=array("error"=>0,1,"message"=>),error =1 ,表示成功，为0，表示失败，message为返回信息
		$ret="{\"statue\":\"".$result['error']."\",\"msg\":\"".$result['message']."\"}";
		exit($ret);
		/* $where['gpp.isactive']=1;
		$where['gpp.endtime']=array("gt",date("Y-m-d H:i:s"));
		$m=M("group_purchase_package")->alias("gpp")->join(" inner join ".$dbprefix."group_package_goods as gpg on gpp.packageid = gpg.packageid ")->where($where)->field("gpp.packagename,gpp.total,gpp.endtime,gpp.packageid,gpg.goodsid")->select();
		//根据orderid 去查询购买的商品的数量
		//var_dump($m);
		foreach($m as $key => $val)
		{
			$ids.="'".$val['packageid']."',";
		}
		$ids=rtrim($ids,",");
		$order=M("group_package_order")->where(" status = 1 and packageid in (".$ids.")")->field("packageid,orderid,userid")->select();
		//var_dump($order);
		if($order)
		{
			//根据orderid 去查询购买的商品的数量
			foreach($order as $key => $val)
			{
				$orderid.="'".$val['orderid']."',";
			}
			$orderid=rtrim($orderid,",");
			$order_goods=M("order_goods")->where(" is_on_pre_sale > 1 and order_id in (".$orderid.")")->field("order_id,goods_id,goods_number")->select();
			//var_dump($order_goods);
		}
		foreach($order as $key => $val)
		{   $i=0;
			foreach($order_goods as $key1 => $val1)
			{   
				if($val['orderid'] == $val1['order_id'])
				{
					
					$goods[$i]['goodsid']=$val1['goods_id'];
					$goods[$i]['goodsnumber']=$val1['goods_number'];
					$i++;
				}
				
			}
			$order[$key]['goods']=$goods;
			$goods="";
		}
		
		foreach($m as $key => $val)
		{
			foreach($order as $key1 => $val1)
			{
				if($val['packageid'] == $val1['packageid'])
				{
					
				}
			}
		}
		var_dump($order); */
		
		
		
	}
	
	
	
	
	
}